﻿CREATE VIEW usix.v_CustomerFile_Export_10
AS
SELECT     CF_E.ApplID, CF_E.Status, CF_E.DaysPastDue, CF_E.LoanID, CF_E.[Customer #], CF_E.[Open Field#01], CF_E.VantageID, CF_E.SSN, CF_E.VIN, 
                      CF_E.Serial#, CF_E.StatusID, CF_E.[G_L Date], CF_E.GL_Days, CF_E.[Open Date], CF_E.Name, CF_E.Address, CF_E.City, CF_E.St, CF_E.[Zip Code], 
                      CF_E.[Home Phone #], CF_E.[Work Phone #], CF_E.[Car Year], CF_E.Make, CF_E.Model, CF_E.[Pymnt Schedule], CF_E.[# of Payments], 
                      CF_E.[# of PaymentsM], CF_E.[Payment Amount], CF_E.[Total Payments], CF_E.[1st_ Payment Date], CF_E.FirstPaymentDateCalc, 
                      CF_E.[Last Payment Date], CF_E.[Next Payment Date], CF_E.[# Pmts Remaining], CF_E.[# Pmts RemainingM], CF_E.[Balance Remaining], 
                      CF_E.[Total Sales Price], CF_E.[Finance Charges], CF_E.[Status Code], CF_E.P_O_E_, CF_E.Loan#_Comments, CF_E.[Primary SSN], 
                      CF_E.[Amt_ Fin__Disc_ Amt], CF_E.[Interest Income], CF_E.[Earned Discount], CF_E.[Dealer Discount], CF_E.[Dealer Proceed], CF_E.[Dealer Code], 
                      usix.t_Dealer.Name AS Dealer, usix.t_Dealer.[Phone #] AS DealerPhone, CF_E.[Principal Balance], CF_E.APR, CF_E.Mileage, CF_E.[Collector Id#], 
                      CF_E.[Discount%], CF_E.[Origination State Code], CF_E.Portfolio#, CF_E.[Sales Rep_], CF_E.[Investor Client], CF_E.[Date L_C], CF_E.[Joint Name], 
                      CF_E.[Joint Address], CF_E.[Joint City], CF_E.[Joint State], CF_E.[Joint Zipcode], CF_E.[Joint SSN], CF_E.[Joint H Phone#], CF_E.[Joint W Phone#], 
                      CF_E.[Primary Cell#], CF_E.[Joint Cell#], CF_E.[Partial Payment], CF_E.[NSF Balance], CF_E.[Late Chg Bal], CF_E.[Apply Late Fee], CF_E.Branch#, 
                      CF_E.CompanyID, CF_E.CompanyID_Raw, CF_E.CompanyName, CF_E.[Bankruptcy Filed Y_N?], CF_E.[Bankruptcy Type 07_13?], 
                      CF_E.[Total Principal], CF_E.DPD, CF_E.DPD0, CF_E.DSLP, CF_E.PastDuePayments, CF_E.PastDueAmount, CF_E.PaymentsMade, 
                      CF_E.PaymentsMadeM, CF_E.PaymentsExpectedM, CF_E.PaymentsBehindM, CF_E.DownPayment, CF_E.[Princ/ChOff Ballance], CF_E.[Repo Date], 
                      CF_E.[A_R Balance], CF_E.[Resale Amt_], CF_E.Chargeoff, CF_E.[Final Repo Date], CF_E.Retainage, CF_E.[Other Chgs Bal], CF_E.UsixID, 
                      CF_E.[Title Received Y_N?], CF_E.NDay, CF_E.[Due Date], CF_E.DueDateSince, CF_E.DueAfterHour, CF_E.DateDueBy, CF_E.NoteUser, 
                      CF_E.TimeZone, CF_E.TimeZoneOffset, CH.Nick AS ContractHolder, CF_E.DaysToPTP, CF_E.DaysToPTP_Sort, CF_E.DaysToPTP_Real, 
                      CF_E.PTP_Agent, CF_E.DPDCalc, CF_E.NoteStatusDate, CF_E.NoteDate, CF_E.[Simple_Add-On], CF_E.VINCheck, CF_E.LateCharges, 
                      CF_E.LatePmtsCount, CF_E.RemBalance78, CF_E.PayOff_TP, CF_E.PayOff_Calc, CF_E.PerDiem, ISNULL(dbo.t_BuyBack.StatusID, 
                      dbo.fs_StateID__BuyBack__InActive()) AS StatusID_BuyBack, ISNULL(S_BB.Name, N'') AS Status_BuyBack, ISNULL(dbo.t_Car.TrackerTypeID, 0) 
                      AS TrackerTypeID, CF_E.NoteDate1, CF_E.StatusID_Collection, CF_E.Status_Collection, CF_E.StatusUser_Collection, 
                      usix.t_PaymentNSF.[Payment Date] AS NSF_Date, ISNULL(usix.t_PaymentNSF.Count, 0) AS NSF_Count, 
                      ISNULL(usix.t_PaymentNSF.[Payment Amount], 0) AS NSF_Amount, DATEDIFF(dd, usix.t_PaymentNSF.[Payment Date], GETDATE()) AS NSF_DaysSince, 
                      ISNULL(VT.TruckDuty, 'U') AS TruckDuty, UN.UserName, CI.BookValueCurrent, CI.BookValueCurrentTypeID, CI.BookValueCurrentDate, 
                      CBP_VT.Description AS BookValueCurrentDesc, CONVERT(int, CF_E.[Total Principal] / NULLIF (CI.BookValueCurrent, 0) * 100) AS PLTV, 
                      dbo.t_Loan.GUID_Documents, CF_E.PaymentsMadeByPrinBal, CF_E.NameLast, CF_E.NameFirst, dbo.tzip_Counties_ALL.CountyName AS County, 
                      CF_E.PaymentAmountM, CF_E.StatusID_Repo, CF_E.Status_Repo, ISNULL(dbo.v_DefermentsInOneYear.Count, 0) AS DefermentsCount, 
                      CF_E.StatusDate_Repo, CF_E.StatusUser_Repo, CF_E.RepoAgent, CF_E.PhoneCell, CF_E.PhoneOther, CF_E.NoteLastDate, CF_E.DaysSinceLastNote, 
                      CF_E.TeamRange, CF_E.DoCollect, CF_E.DateDueBy_Priority, CF_E.StatusID_BK, CF_E.Status_BK, CF_E.StatusDate_BK, CF_E.StatusUser_BK, 
                      SH.StatusFrom AS StatusCodePrev, SH.Date AS StatusCodeDate
FROM         usix.v_CollectionList AS CF_E LEFT OUTER JOIN
                      usix.v_StatusHistory AS SH ON CF_E.[Customer #] = SH.[Customer #] AND CF_E.CompanyID_Raw = SH.CompanyID LEFT OUTER JOIN
                      dbo.v_DefermentsInOneYear ON CF_E.CompanyID_Raw = dbo.v_DefermentsInOneYear.CompanyID AND 
                      CF_E.UsixID = dbo.v_DefermentsInOneYear.[Id #] LEFT OUTER JOIN
                      dbo.v_Repossession AS REPO ON CF_E.LoanID = REPO.LoanID LEFT OUTER JOIN
                      dbo.t_BuyBack INNER JOIN
                      dbo.t_State AS S_BB ON dbo.t_BuyBack.StatusID = S_BB.ID ON CF_E.LoanID = dbo.t_BuyBack.LoanID LEFT OUTER JOIN
                      dbo.v_Borrower_Update AS B0 INNER JOIN
                      dbo.v_CarBookProvider_ValueType AS CBP_VT INNER JOIN
                      dbo.t_ContractHolder AS CH INNER JOIN
                      dbo.t_Loan ON CH.ID = dbo.t_Loan.ContractHolderID AND CH.ID = dbo.t_Loan.ContractHolderID INNER JOIN
                      dbo.t_State AS S ON dbo.t_Loan.StatusID = S.ID INNER JOIN
                      dbo.t_CarInfo AS CI ON dbo.t_Loan.ID = CI.LoanID AND dbo.t_Loan.ID = CI.LoanID AND dbo.t_Loan.ID = CI.LoanID ON 
                      CBP_VT.ID = CI.BookValueCurrentTypeID INNER JOIN
                      dbo.t_Loan_Borrower AS L_B0 ON dbo.t_Loan.ID = L_B0.LoanID AND L_B0.Pos = 0 ON B0.ID = L_B0.BorrowerID ON 
                      dbo.t_Loan.ID = CF_E.LoanID LEFT OUTER JOIN
                      dbo.t_State AS S_Collection INNER JOIN
                      dbo.t_Collection ON S_Collection.ID = dbo.t_Collection.StatusID ON CF_E.LoanID = dbo.t_Collection.LoanID LEFT OUTER JOIN
                      dbo.tzip_99 LEFT OUTER JOIN
                      dbo.tzip_Counties_ALL ON dbo.tzip_99.COUNTY = dbo.tzip_Counties_ALL.CountyFIPS AND dbo.tzip_99.STATE = dbo.tzip_Counties_ALL.StateFIPS ON 
                      LEFT(CF_E.[Zip Code], 5) = dbo.tzip_99.ZIP_CODE LEFT OUTER JOIN
                      usix.t_PaymentNSF ON CF_E.CompanyID_Raw = usix.t_PaymentNSF.CompanyID AND CF_E.UsixID = usix.t_PaymentNSF.[Id #] LEFT OUTER JOIN
                      dbo.ts_State INNER JOIN
                      dbo.ts_TimeZone ON dbo.ts_State.TimeZone = dbo.ts_TimeZone.TimeZone AND dbo.ts_State.TimeZone = dbo.ts_TimeZone.TimeZone AND 
                      dbo.ts_State.TimeZone = dbo.ts_TimeZone.TimeZone ON CF_E.St = dbo.ts_State.StateCode LEFT OUTER JOIN
                      dbo.t_VehicleType_Clean AS VT INNER JOIN
                      dbo.t_Car ON VT.VIN_8 = dbo.t_Car.VIN_8 ON CF_E.VIN = dbo.t_Car.VIN LEFT OUTER JOIN
                      dbo.v_Note_PTP_Open AS N_PTP_O ON CF_E.LoanID = N_PTP_O.LoanID LEFT OUTER JOIN
                      dbo.v_Note_Last AS N_L ON CF_E.LoanID = N_L.LoanID LEFT OUTER JOIN
                      usix.t_Dealer ON CF_E.CompanyID_Raw = usix.t_Dealer.CompanyID AND CF_E.[Dealer Code] = usix.t_Dealer.[Dealer Code] CROSS JOIN
                          (SELECT     dbo.UserName() AS UserName) AS UN
WHERE     (ISNULL(dbo.t_Loan.ContractHolderID, 0) IN
                          (SELECT     CHID
                            FROM          dbo.fs_ContractHolders() AS fs_ContractHolders_1))

GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPane1', @value = N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[40] 4[34] 2[17] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1[50] 4[26] 3) )"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1[50] 2[25] 3) )"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4[58] 2[12] 3) )"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2[66] 3) )"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4[50] 3) )"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1[43] 4) )"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[55] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4[60] 2) )"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4) )"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2) )"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = -213
         Left = 0
      End
      Begin Tables = 
         Begin Table = "CF_E"
            Begin Extent = 
               Top = 278
               Left = 294
               Bottom = 658
               Right = 493
            End
            DisplayFlags = 280
            TopColumn = 114
         End
         Begin Table = "SH"
            Begin Extent = 
               Top = 219
               Left = 577
               Bottom = 327
               Right = 728
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "v_DefermentsInOneYear"
            Begin Extent = 
               Top = 435
               Left = 121
               Bottom = 543
               Right = 272
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "REPO"
            Begin Extent = 
               Top = 159
               Left = 841
               Bottom = 290
               Right = 992
            End
            DisplayFlags = 344
            TopColumn = 0
         End
         Begin Table = "t_BuyBack"
            Begin Extent = 
               Top = 203
               Left = 866
               Bottom = 311
               Right = 1021
            End
            DisplayFlags = 344
            TopColumn = 0
         End
         Begin Table = "S_BB"
            Begin Extent = 
               Top = 277
               Left = 890
               Bottom = 385
               Right = 1041
            End
            DisplayFlags = 344
            TopColumn = 0
         End
         Begin Table = "B0"
            Begin Extent = 
               Top = 659
               Left = 921
               Bottom = 836
               Right = 1143
            End
            DisplayF', @level0type = N'SCHEMA', @level0name = N'usix', @level1type = N'VIEW', @level1name = N'v_CustomerFile_Export_10';


GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPane2', @value = N'lags = 280
            TopColumn = 0
         End
         Begin Table = "CBP_VT"
            Begin Extent = 
               Top = 614
               Left = 920
               Bottom = 722
               Right = 1071
            End
            DisplayFlags = 344
            TopColumn = 4
         End
         Begin Table = "CH"
            Begin Extent = 
               Top = 376
               Left = 606
               Bottom = 669
               Right = 707
            End
            DisplayFlags = 344
            TopColumn = 0
         End
         Begin Table = "t_Loan"
            Begin Extent = 
               Top = 532
               Left = 579
               Bottom = 640
               Right = 759
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "S"
            Begin Extent = 
               Top = 496
               Left = 871
               Bottom = 604
               Right = 1022
            End
            DisplayFlags = 344
            TopColumn = 0
         End
         Begin Table = "CI"
            Begin Extent = 
               Top = 557
               Left = 836
               Bottom = 744
               Right = 1038
            End
            DisplayFlags = 344
            TopColumn = 10
         End
         Begin Table = "L_B0"
            Begin Extent = 
               Top = 627
               Left = 744
               Bottom = 743
               Right = 895
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "S_Collection"
            Begin Extent = 
               Top = 46
               Left = 929
               Bottom = 154
               Right = 1080
            End
            DisplayFlags = 344
            TopColumn = 0
         End
         Begin Table = "t_Collection"
            Begin Extent = 
               Top = 35
               Left = 730
               Bottom = 189
               Right = 881
            End
            DisplayFlags = 344
            TopColumn = 0
         End
         Begin Table = "tzip_99"
            Begin Extent = 
               Top = 308
               Left = 758
               Bottom = 471
               Right = 909
            End
            DisplayFlags = 344
            TopColumn = 0
         End
         Begin Table = "tzip_Counties_ALL"
            Begin Extent = 
               Top = 402
               Left = 952
               Bottom = 510
               Right = 1103
            End
            DisplayFlags = 344
            TopColumn = 0
         End
         Begin Table = "t_PaymentNSF (usix)"
            Begin Extent = 
               Top = 6
               Left = 468
               Bottom = 114
               Right = 630
            End
            DisplayFlags = 344
            TopColumn = 0
         End
         Begin Table = "ts_State"
            Begin Extent = 
               Top = 276
               Left = 85
               Bottom = 369
               Right = 236
            End
            DisplayFlags = 344
            TopColumn = 0
         End
         Begin Table = "ts_TimeZone"
            Begin Extent = 
               Top = 393
               Left = 40
               Bottom = 486
               Right = 191
            End
            DisplayFlags = 344
            TopColumn = 0
         End
         Begin Table = "VT"
            Begin Extent = 
               Top = 90
               Left = 9
               Bottom = 168
               Right = 110
            End
            DisplayFlags = 344
            TopColumn = 0
         End', @level0type = N'SCHEMA', @level0name = N'usix', @level1type = N'VIEW', @level1name = N'v_CustomerFile_Export_10';


GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPane3', @value = N'Begin Table = "t_Car"
            Begin Extent = 
               Top = 65
               Left = 133
               Bottom = 144
               Right = 336
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "N_PTP_O"
            Begin Extent = 
               Top = 231
               Left = 388
               Bottom = 339
               Right = 539
            End
            DisplayFlags = 344
            TopColumn = 0
         End
         Begin Table = "N_L"
            Begin Extent = 
               Top = 429
               Left = 687
               Bottom = 512
               Right = 853
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "t_Dealer (usix)"
            Begin Extent = 
               Top = 196
               Left = 422
               Bottom = 304
               Right = 573
            End
            DisplayFlags = 344
            TopColumn = 0
         End
         Begin Table = "UN"
            Begin Extent = 
               Top = 396
               Left = 727
               Bottom = 459
               Right = 878
            End
            DisplayFlags = 344
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 168
         Width = 284
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 15', @level0type = N'SCHEMA', @level0name = N'usix', @level1type = N'VIEW', @level1name = N'v_CustomerFile_Export_10';


GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPane4', @value = N'00
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 9225
         Alias = 1830
         Table = 1260
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 4500
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End', @level0type = N'SCHEMA', @level0name = N'usix', @level1type = N'VIEW', @level1name = N'v_CustomerFile_Export_10';


GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPaneCount', @value = 4, @level0type = N'SCHEMA', @level0name = N'usix', @level1type = N'VIEW', @level1name = N'v_CustomerFile_Export_10';

